#!/usr/bin/env python
# -*- coding: utf-8 -*-

#python operate mysql database
import MySQLdb
import time

time1=time.time()

DATABASE_NAME = '1000genomes_20130502'
HOST = '10.10.31.17'
PORT = '3306'
USER_NAME = '1000genomes'
PASSWORD = '1000genomes'
CHAR_SET = 'utf8'

INFILE="/local/data/backup/ycling/1000genomes/ftp-trace.ncbi.nih.gov/1000genomes/ftp/release/20130502/ALL.autosomes.phase3_shapeit2_mvncall_integrated_v3.20130502.sites.vcf"

conn=MySQLdb.connect(host = HOST, user = USER_NAME, passwd = PASSWORD, db = DATABASE_NAME, charset = CHAR_SET)

inputfile=open(INFILE,"r")
for line in inputfile:
	line=line.rstrip()
	if not line.startswith("#"):
		splitline=line.split("\t")
		chrom=splitline[0]
		pos=splitline[1]
		ref_id=splitline[2]
		ref=splitline[3]
		alt=splitline[4]
		qual=splitline[5]
		filt=splitline[6]
		info=splitline[7]
		cursor=conn.cursor()
		sql="insert into 1000genomes_20130502_GRCh37_20140925_chr"+chrom+" values(0,%s,%s,%s,%s,%s,%s,%s,%s)"
		params=(chrom,pos,ref_id,ref,alt,qual,filt,info)
		result=cursor.execute(sql,params)
		conn.commit()
		cursor.close()
conn.close()
inputfile.close()

time2=time.time()

print(time2-time1)